#! /bin/bash
PRESTO_HOME=/opt/cloudera/parcels/presto/bin/presto

if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi

/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute "
insert into hive.zx_dwb.dwb_apply_detail
select dfcra.id,
       dfcra.create_date_time,
       dfcra.update_date_time,
       dfcra.deleted,
       dfcra.customer_id,
       dfcra.business_scrm_department_id,
       dfcra.origin_type,
       dfcra.itcast_school_id,
       dfcra.itcast_subject_id,
       dfcra.creator,
       dfcra.origin_channel,
       dfcra.payment_state,
       dfcra.payment_time,
       dfcra.signup_state,
       dfcra.signup_time,
       dfcra.deleter,
       ddcaa.customer_relationship_first_id,
       ddcaa.appeal_status,
       ddea.tdepart_id,
       ddisa1.name as school_name,
       ddisa.name  as subject_name,
       ddsda.name  as department_name,
       dfcra.dt
from hive.zx_dwd.dwd_fact_customer_relationship_apply dfcra
         left join zx_dwd.dwd_dim_itcast_school_apply ddisa1
                   on dfcra.itcast_school_id = ddisa1.id and dfcra.dt = '${TD_DATE}'
         left join zx_dwd.dwd_dim_itcast_subject_apply ddisa
                   on dfcra.itcast_subject_id = ddisa.id and dfcra.dt = '${TD_DATE}'
         left join zx_dwd.dwd_dim_employee_apply ddea
                   on dfcra.creator = ddea.id and dfcra.dt = '${TD_DATE}'
         left join zx_dwd.dwd_dim_scrm_department_apply ddsda
                   on ddea.tdepart_id = ddsda.id
         left join zx_dwd.dwd_dim_customer_appeal_apply ddcaa
                   on dfcra.id = ddcaa.customer_relationship_first_id and dfcra.dt = '${TD_DATE}' and ddcaa.dt = '${TD_DATE}'
;"